IF EXISTS (
	SELECT SPECIFIC_NAME 
	FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_SCHEMA = N'testing'
		AND SPECIFIC_NAME = N'prc_GetPrivateUserQuestionGroups' 
)
	BEGIN
		DROP PROCEDURE testing.prc_GetPrivateUserQuestionGroups
		PRINT 'Dropping testing.prc_GetPrivateUserQuestionGroups'
	END
GO

/*-------------------------------------------------------------------------------------
** Name:	prc_GetPrivateUserQuestionGroups
** Purpose:	Get all question groups are post by user
** 
** ------------------------------------------------------------------------------------
** Input Parameters: 
** @UserId
** @PageSize
** @PageNum
** N/A
** 
** ------------------------------------------------------------------------------------
** Usage
** EXEC testing.prc_GetPrivateUserQuestionGroups 1, 5, 1
**
** ------------------------------------------------------------------------------------
** Author:    Hau
** Date:      2012-07-02
** Database:  SIVN_OnlineTestingEngine
**
** ------------------------------------------------------------------------------------
** Modifications:
** 2012-07-02	Hau		Create
** ----------------------------------------------------------------------------------*/

CREATE PROCEDURE [testing].[prc_GetPrivateUserQuestionGroups]
@UserId int,
@PageSize int,
@PageNum int
AS
BEGIN
	SET NOCOUNT ON;
	
	WITH QGsRN AS
	(
    SELECT ROW_NUMBER() OVER(ORDER BY qg.ModifiedDate, qg.Id) AS RowNum
          ,qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
		qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
		(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
		(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes',
		(select COUNT(Id) from testing.QuestionGroupDetail where QuestionGroupId = qg.Id) 'TotalQuestion'
	FROM testing.QuestionGroup qg
	where qg.UserId = @UserId
	)
	
	SELECT * FROM QGsRN
	WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
	ORDER BY ModifiedDate, Id
	
	SELECT @@Identity              
  
END

GO

PRINT 'Created [testing].[prc_GetUserQuestionGroups]'
GO